package DataLayer;
/**
 * @author: Lam Thanh Trung 1101706
 * @purpose: Lop chua cac phuong thuc xu ly database
 * @Date: 18/9/2013
 */
import BusinessLogic.Encrypt;
import Presentation.frmServer;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Database {
    
    private static ChatQuery cq;
    private static ChatLogin clog;
    private static frmServer myFrame;
    
    //phuong thuc tao nguoi dung moi
    public boolean addUser(String username, String password){
        try{
           if(!existUser(username)){
               String str="insert into User(Username, Password) values('"+username+"', '"+Encrypt.pass(password)+"')";
               cq = new ChatQuery();
               cq.executeSQL(str);
               cq.closeCon();
               return true;
           }
           else{
               cq.closeCon();
               return false;
           }
        }
        catch(Exception e){
            return false;
        }
    }
    //phuong thuc thay doi mat khau nguoi dung
    public void changePass(String username, String password){
        try{
            String str="Update `User` set `Password` = '"+Encrypt.pass(password)+"' where `Username` = '"+username+"'";
            cq = new ChatQuery();
            cq.executeSQL(str);
            cq.closeCon();
        }
        catch(Exception e){
        }
    }
    //phuong thuc thong bao dang nhap va xu ly trang thai nguoi dung
    public boolean loginState(String username, String password, int status){
        try{
            if(clog.Login(username, password)){
                return true;
            }
            else{
                return false;
            }
        }
        catch(Exception e){
            return false;
        }
    }
    //phuong thuc thong bao dang xuat va xu ly trang thai nguoi dung
    public boolean logoutState(String username, String password){
        try{
            if(!clog.Login(username, password)){
                return true;
            }
            else{
                return false;
            }
        }
        catch(Exception e){
            return false;
        }
    }
    //phuong thuc tra ve trang thai nguoi dung // 0: offline  // 1: hiện   // 2: bận 
    public static int status(String username){
        int state=0;
        try{
            String str="Select Status from User where Username = '"+ username+"'";
            cq=new ChatQuery();
            ResultSet rs=cq.getRs(str);
            if(rs.next()){
                state=rs.getInt("Status");
            }
            cq.closeCon();
        }
        catch(Exception e){
        }
        return state;
    }
    
    //phuong thuc luu trang thai nguoi dung // 0: offline  // 1: hiện   // 2: bận 
    public void setStatus(String username, String state){
        int status = Integer.parseInt(state);
        try{
            String str="Update User set Status = "+ status +" where Username = '"+ username+"'";
            cq=new ChatQuery();
            cq.executeUpdate(str);
            cq.closeCon();
        }
        catch(Exception e){
        }
    }
    //Lấy danh sách friend bị khóa
    public String getFriendIsLock(String username){
        try{     
            String str="SELECT `Friendname` FROM  `friendlist` WHERE  `Username` =  '"+username+"' AND  `Lock` =1";
            cq = new ChatQuery();
            ResultSet rs = cq.getRs(str);
            String st="";
            while(rs.next()){
                st+= rs.getString(1)+((char)001);
            }
            cq.closeCon();
            return st;
        }
        catch(Exception e){
            return null;
        }
    }
    //phuong thuc them 1 nguoi ban vao danh sach ban
    public void addFriends(String username, String friendname){
        try{
           if(!isFriend(username,friendname)){
                String str="Insert into Friendlist(Username, Friendname) values('"+username+"', '"+friendname+"')";
                cq = new ChatQuery();
                cq.executeSQL(str);
                cq.closeCon();
           }
        }
        catch(Exception e){
        }
    }
    
    //phuong thuc luu yeu cau ket ban
    public void saveFriendRequest(String username, String friendname){
        try{
           if(!isFriend(username,friendname)){
                String str="Insert into requestfriend(FromUser, ToUser) values('"+username+"', '"+friendname+"')";
                cq = new ChatQuery();
                cq.executeSQL(str);
                cq.closeCon();
           }
        }
        catch(Exception e){
        }
    }
    //phuong thuc xoa yeu cau ket ban
    public void deleteFriendRequest(String username, String friendname){
        try{
                String str="Delete from requestfriend where fromuser= '"+ username +"' and touser = '"+friendname+"'";
                cq = new ChatQuery();
                cq.executeSQL(str);
                cq.closeCon();
        }
        catch(Exception e){
        }
    }
    //phương thức kiem tra co yeu cau ket ban
    public boolean checkFriendRequest(String user){
        try{
            String str="select * from requestfriend where Touser = '"+ user +"'";
            cq = new ChatQuery();
            cq.getRs(str);
            ResultSet rs = cq.getRs(str);
            if(rs.next()){
                cq.closeCon();
                return true;
            }
            else{
                cq.closeCon();
                return false;
            }
        }
        catch(Exception e){
            return false;
        }
    }
    //phương thức lấy về danh sách user gửi yêu cầu kết bạn
    public String getListUserRequest(String friend){
        try{
            String listuser="";
            String str="select distinct Fromuser from requestfriend where touser = '"+friend+"'";
            cq = new ChatQuery();
            cq.getRs(str);
            ResultSet rs = cq.getRs(str);
            while(rs.next()){
                listuser+=rs.getString(1)+((char)001);
            }
            cq.closeCon();
            return listuser;
        }
        catch(Exception e){
            return null;
        }
    }
    //phương thức lưu tin nhắn offline
    public void messageOffline(String username, String friend, String message){
        try{
            String str="INSERT INTO .`offlinemessage` (`NoOff`, `Username`, `Friendname`, `Offmessage`) VALUES (NULL, '"+username+"','"+friend+"','"+message+"')";
            cq = new ChatQuery();
            cq.executeSQL(str);
            cq.closeCon();
        }
        catch(Exception e){
        }
    }
    //phuong thuc dem so lan ma ma nguoi gui tin nhắn đến người nhận
    public static int loopCountOffMessageOffline(String username, String friendname){
       int n=0;
       try{
            String str="select count(*) from offlinemessage where Username = '"+username+"' and Friendname = '"+friendname+"'";
            cq = new ChatQuery();
            cq.getRs(str);
            ResultSet rs = cq.getRs(str);
            if(rs.next()){
                n=rs.getInt(1);
            }
       }
       catch(Exception e){}
       cq.closeCon();
       return n;
    }
    //phương thức lấy tin nhắn offline
    public String[] getMessageOffline(String username, String friendname){
        try{
            int i=0;
            String[] chuoi=new String[loopCountOffMessageOffline(username, friendname)];
            String str="select * from offlinemessage where Username = '"+username+"' and Friendname = '"+friendname+"'";
            cq = new ChatQuery();
            cq.getRs(str);
            ResultSet rs = cq.getRs(str);
            while(rs.next()){
               chuoi[i]=rs.getString(2)+((char)001)+rs.getString(3)+((char)002)+rs.getString(4);
               i++;
            }
            cq.closeCon();
            return chuoi;
        }
        catch(Exception e){
            return null;
        }
    }
    //phương thức lấy về danh sách user gửi tin nhắn offline đến user nhận
    public String getListUserSendMessageOffline(String friend){
        try{
            String listuser="";
            String str="select distinct Username from offlinemessage where Friendname = '"+friend+"'";
            cq = new ChatQuery();
            cq.getRs(str);
            ResultSet rs = cq.getRs(str);
            while(rs.next()){
                listuser+=rs.getString(1)+((char)001);
            }
            cq.closeCon();
            return listuser;
        }
        catch(Exception e){
            return null;
        }
    }
    //phuong thức xóa những tin nhắn offline đã xem xong
    public boolean deleteMessageOffline(String friend, String name){
        try{
            String str="delete from offlinemessage where Username = '"+friend+"' and Friendname = '"+name+"'";
            cq= new ChatQuery();
            cq.executeSQL(str);
            return true;
        }
        catch(Exception e){
            return false;
        }
    }
    //phương thức kiểm tra user có tin nhắn offline hay không?
    public boolean checkOfflineMessage(String user){
        try{
            String str="select * from offlinemessage where Friendname = '"+ user +"'";
            cq = new ChatQuery();
            cq.getRs(str);
            ResultSet rs = cq.getRs(str);
            if(rs.next()){
                cq.closeCon();
                return true;
            }
            else{
                cq.closeCon();
                return false;
            }
        }
        catch(Exception e){
            return false;
        }
    }
    //Phương thức kiểm tra có cho phép đăng nhập
    public boolean login(String username, String password){
        try{
            String str="Select Username, Password, Status from User where Username = '"+ username+"' and Password= '"+Encrypt.pass(password)+"'";
            cq = new ChatQuery();
            cq = new ChatQuery();
            ResultSet rs=cq.getRs(str);
            if(rs.next()){
                if(rs.getInt(3)==0){
                    str="Update User Set Status = 1 where Username = '"+ username+"'";
                    cq.executeUpdate(str);
                    cq.closeCon();
                return true;
                }
                else return false;
            }
            else{
                cq.closeCon();
                return false;
            }
        }
        catch(Exception e){
            return false;
        }
    }
    //Phương thức kiểm tra có cho phép đăng nhập
    public void logout(String username){
        try{     
                String str="Update User Set Status = 0 where Username = '"+ username+"'";
                cq = new ChatQuery();
                cq.executeUpdate(str);
                cq.closeCon();
           
        }
        catch(Exception e){
        }
    }
    //Phương thức kiểm tra tồn tại username
    public boolean existUser(String username){
        try{     
            String str="Select username From User where Username = '"+ username+"'";
            cq = new ChatQuery();
            ResultSet rs = cq.getRs(str);
            if(rs.next()){
                cq.closeCon();
                return true;
            }
            else{
                cq.closeCon();
                return false;
            }
        }
        catch(Exception e){
            return false;
        }
    }
    //Phương thức lấy danh sách bạn
    public String getFriendList(String username){
        try{     
            String str="Select friendname From friendlist where Username = '"+ username+ "'";
            cq = new ChatQuery();
            ResultSet rs = cq.getRs(str);
            String st="";
            int state;
            while(rs.next()){
                state = status(rs.getString(1));
                if(state==3) state=0;
                st+= state+rs.getString(1)+((char)001);
            }
            cq.closeCon();
            return st;
        }
        catch(Exception e){
            return null;
        }
    }
    //Phương thức lấy danh sách bạn đang online
    public static String getListFriendOnline(String username){
        try{     
            String str="Select friendname From friendlist where Username = '"+ username+ "'";
            cq = new ChatQuery();
            ResultSet rs = cq.getRs(str);
            String st="";
            while(rs.next()){
                 if(status(rs.getString(1))!=0){
                    st+=rs.getString(1)+((char)001);
                } 
            }
            cq.closeCon();
            return st;
        }
        catch(Exception e){
            return null;
        }
    }
    
    //phuong thuc kiem tra xem username nay co phai la ban chua
    public boolean isFriend(String username, String friendname){
        try{
            String str="Select Username, Friendname from Friendlist where Username = '"+ username+"' and Friendname = '"+ friendname +"'";
            cq = new ChatQuery();
            ResultSet rs=cq.getRs(str);
            if(rs.next()){
                cq.closeCon();
                return true;
            }
            else{
                cq.closeCon();
                return false;
            }
        }
        catch(Exception e){
            return false;
        }
    }
    //phuong thuc xoa 1 nguoi ra khoi danh sach ban
     public boolean deleteFriend(String username, String friedname){
        try{
            String str="Delete from Friendlist where Username = '"+username+"' and Friendname = '"+friedname+"'";
            cq = new ChatQuery();
            cq.executeSQL(str);
            cq.closeCon();
            return true;
        }
        catch(Exception e){
            return false;
        }
    }
    //phuong thuc tim ban tren server trong csdl
    public String findFriend(String username){
        try{     
            String str="Select Username From User where Username like '%"+ username +"%'";
            cq = new ChatQuery();
            ResultSet rs = cq.getRs(str);
            String st="";
            while(rs.next()){
                st+=rs.getString(1)+((char)001);
            }
            cq.closeCon();
            return st;
        }
        catch(Exception e){
            return null;
        }
    }
     
    //phuong thuc kiểm tra ND có bị khóa chưa?
    public boolean checkLocked(String username, String friendname){
        try{
            int a=0;
            String str="select  *  from  `Friendlist` where  `Username` =  '"+username+"' and  `Friendname` =  '"+friendname+"'";                   
            cq = new ChatQuery();
            cq.getRs(str);
            ResultSet rs = cq.getRs(str);
            while(rs.next()){
                a=rs.getInt(3);
            }
            cq.closeCon();
            if(a==0){
                return false;
            }
            else{
                return true;
            }
        }
        catch(Exception e){
            return false;
        }
    }
    //phuong thuc khoa nguoi dung tu 1 nguoi dung khac // 0: khoa // 1: khong khoa
    public void lockFriend(String username, String friendname){
        try{
            String str="Update `Friendlist` set `Lock` = '1' where `Username` = '"+username+"' and `Friendname` = '"+friendname+"'";
            cq = new ChatQuery();
            cq.executeUpdate(str);
            cq.closeCon();
        }
        catch(Exception e){
        }
    }
    //phuong thuc mở khoa nguoi dung // 0: khoa // 1: khong khoa
    public void UnlockFriend(String username, String friendname){
        try{
            String str="Update `Friendlist` set `Lock` = '0' where `Username` = '"+username+"' and `Friendname` = '"+friendname+"'";
            cq = new ChatQuery();
            cq.executeUpdate(str);
            cq.closeCon();
        }
        catch(Exception e){
        }
    }
    //phương thức kiểm tra group có tồn tại hay chưa?
    public boolean existsGroup(String group){
        try{
            String str="SELECT * FROM  `group` WHERE  `GroupName` =  '"+group+"'";
            cq = new ChatQuery();
            ResultSet rs = cq.getRs(str);
            if(rs.next()){
                cq.closeCon();
                return true;
            }
            else{
                cq.closeCon();
                return false;
            }
        }
        catch(Exception e){
            myFrame.addLog(e.getMessage());
            return false;
        }
    }
    //phương thức kiểm tra co tham gia nhom hay chua
    public boolean checkJoinGroup(String user){
        try{
            String str="Select * from `join` where Username = '"+user+"'";
            cq = new ChatQuery();
            ResultSet rs = cq.getRs(str);
            if(rs.next()){
                cq.closeCon();
                return true;
            }
            else{
                cq.closeCon();
                return false;
            }
        }
        catch(Exception e){
            myFrame.addLog(e.getMessage());
            return false;
        }
    }
    
    //phương thức tạo thêm group mới
    public void createGroup(String group, String user){
        try{
            String str="INSERT INTO `group` (\n" +
                        "`GroupName` ,\n" +
                        "`UserCreate`\n" +
                        ")\n" +
                        "VALUES (\n" +
                        "'"+group+"',  '"+user+"'\n" +
                        ")";
            cq = new ChatQuery();
            cq.executeSQL(str);
            cq.closeCon();
        }
        catch(SQLException e){
            myFrame.addLog("loi1");
        } catch (ClassNotFoundException e) {
             myFrame.addLog("loi2");
        } 
    }
   
    //Thêm thành viên mới vào nhóm
    public void addMember(String user, String group){
        try{
            String str="insert into `join` values('"+user+"','"+group+"')";
            cq = new ChatQuery();
            cq.executeSQL(str);
            cq.closeCon();
            
        }
        catch(ClassNotFoundException e){} catch (SQLException e) {
        }
    }
    //Xóa thành viên ra khỏi nhóm
    public void removeMember(String user, String group){
        try{
            String str="delete from `join` where Username='"+user+"' and Groupname='"+group+"'";
            cq = new ChatQuery();
            cq.executeSQL(str);
            cq.closeCon(); 
        }
        catch(ClassNotFoundException e){} catch (SQLException e) {
        }
    }
     //Xóa thành viên ra khỏi nhóm
    public void removeFromGroup(String user){
        try{
            String str="delete from `join` where Username='"+user+"'";
            cq = new ChatQuery();
            cq.executeSQL(str);
            cq.closeCon(); 
        }
        catch(ClassNotFoundException e){} catch (SQLException e) {
        }
    }
    //Tổng số thành viên trong nhóm
    public int totalMemberInGroup(String gr){
        int t=-1;
        try{
            String str="Select count(*) from `join` where Groupname = '"+gr+"'";
            cq = new ChatQuery();
            ResultSet rs = cq.getRs(str);
            if(rs.next())
                return rs.getInt(1);
            cq.closeCon();
            return t;
        }
        catch(ClassNotFoundException e){
            return t;
        } catch (SQLException e) {
            return t;
        }
    }
    //Lấy tất cả các thành viên trong nhóm
    public String[] getMemberInGroup(String gr){
        int i=0;
        String[] member = new String[totalMemberInGroup(gr)];
        try{
            String str="Select * from `join` where Groupname = '"+gr+"'";
            cq = new ChatQuery();
            ResultSet rs = cq.getRs(str);
            while(rs.next()){
                member[i]=rs.getString(1);
                i++;
            }
            cq.closeCon();
            return member;
        }
        catch(ClassNotFoundException e){
            return null;
        } catch (SQLException e) {
            return null;
        }
    }
    //Tim nhom ma user da tham gia
    public String findGroup(String user){
        try{
            String str="Select * from `join` where Username = '"+user+"'";
            cq = new ChatQuery();
            ResultSet rs = cq.getRs(str);
            if(rs.next()){
                return rs.getString(2);
            }
            cq.closeCon();
            return null;
        }
        catch(ClassNotFoundException e){
            return null;
        } catch (SQLException e) {
            return null;
        }
    }
    
     //Kiem tra da tham gia nhom chat
    public boolean isJoinGroup(String user){
        try{
            String str="Select * from `join` where Username = '"+user+"'";
            cq = new ChatQuery();
            ResultSet rs = cq.getRs(str);
            if(rs.next()){
                return true;
            }
            cq.closeCon();
            return false;
        }
        catch(ClassNotFoundException e){
            return false;
        } catch (SQLException e) {
            return false;
        }
    }
    //Tim nhom ma user da tao
    public String findGroupCreate(String user){
        try{
            String str="Select * from `group` where Usercreate = '"+user+"'";
            cq = new ChatQuery();
            ResultSet rs = cq.getRs(str);
            if(rs.next()){
                return rs.getString(1);
            }
            cq.closeCon();
            return ((char)001)+"";
        }
        catch(ClassNotFoundException e){
            return ((char)001)+"";
        } catch (SQLException e) {
            return ((char)001)+"";
        }
    }
    //Người tạo ra nhóm
    public boolean checkCreateGroup(String user){
        try{
            String str = "Select * from `group` where Usercreate='"+user+"'";
            cq=new ChatQuery();
            ResultSet rs = cq.getRs(str);
            if(rs.next()){
                cq.closeCon();
                return true;
            }
            return false;
        }
        catch(ClassNotFoundException e){
            return false;
        } catch (SQLException e) {
            return false;
        }
    }
    //Xóa nhóm
    public void removeGroup(String user, String group){
        try{
            String str="delete from `join` where Groupname='"+group+"'";
            cq = new ChatQuery();
            cq.executeSQL(str); 
            str="delete from `group` where Usercreate='"+user+"'";
            cq.executeSQL(str);
            cq.closeCon();
        }
        catch(ClassNotFoundException e){} catch (SQLException e) {
        }
    }
    //phuong thuc khoa nguoi dung tu 1 nguoi dung khac // 0: khoa // 1: khong khoa
    public void closedServer(){
        try{
            String str="Update User set Status=0";
            cq = new ChatQuery();
            cq.executeUpdate(str);
            cq.closeCon();
        }
        catch(Exception e){
        }
    }
     
}
